package com.phoenix.jsef.framework;


import com.phoenix.jsef.common.CUtils;
import com.phoenix.jsef.common.DateUtils;
import com.phoenix.jsef.common.ReflectionUtils;
import com.phoenix.jsef.framework.annotations.EntityAttribute;
import com.phoenix.jsef.framework.annotations.EntityCollectionAttribute;
import com.phoenix.jsef.framework.annotations.EntityFieldAttribute;
import com.phoenix.jsef.framework.annotations.EntitySubClassAttribute;
import com.phoenix.jsef.jdbcutils.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.sql.SQLException;
import java.sql.Types;
import java.util.*;

/**
 * @version V1.0
 * @ClassName: AccessComponent
 * @Description:TODO(这个类作为所有数据存取组件的基类，完成数据存取的基本操作)
 * @author: 李启联
 * @date: 2018-01-12 15:31
 * @Copyright: Copyright (c) 2018
 * 申明：本内容未经作者本人同意,禁止外泄以及用于其他的商业项目.
 */
@SuppressWarnings(value={"unchecked","unused"})
public class AccessComponent {

	private static final Logger logger = LoggerFactory.getLogger(AccessComponent.class);

	// 解决并发请求 参数互串问题 (把请求参数封装起来并用ThreadLocal包装)
	private ThreadLocal<AccessParameter> accessParameter = new ThreadLocal<AccessParameter>();

    public AccessParameter getAccessParameter(){
        AccessParameter param = accessParameter.get();
        if(null == param){
            param = new AccessParameter();
            accessParameter.set(param);
        }
        return param;
    }

	// 此线程内共享变量是为了解决递归查询返回主列表的记录数量
	private static ThreadLocal<Integer> tlTotalCount = new ThreadLocal<Integer>();
	

    /**
     * 通用查询方法
     * @param type 返回单个实体对象
     * @return 返回单个实体对象
     * @throws Exception
     */
	public Entity searchEntity(Type type) throws Exception {

		Entity entity = null;

		/// 检查type对应的类是否含有@EntityAttribute注解
		if (Class.forName(type.getTypeName()).isAnnotationPresent(EntityAttribute.class)) {
			EntityAttribute attribute = (EntityAttribute) Class.forName(type.getTypeName())
					.getAnnotation(EntityAttribute.class);
			String selectTable = attribute.selectTable();
			String identityProperty = //attribute.identityProperty();
									  //此写法增强了此注解的灵活性 可以为数据库字段的名称 也可以为实体模型属性名称			
					CUtils.camelToUnderline(
							ReflectionUtils.getPropertyName(Class.forName(type.getTypeName()),CUtils.underlineToCamel(attribute.identityProperty())));

			List<Map<String, Object>> list =null;
			boolean isExitProcedure = SqlHelper.isExitProcedure("SEARCH");

			if(isExitProcedure) {
                DBParameterCollection dbParmCollection = new DBParameterCollection();
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "SOURCETABLE", selectTable));
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "PRIMARYKEY", identityProperty));
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "WHERECLAUSE", this.getAccessParameter().getFilter().toString()));
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "ORDERBYCLAUSE", this.getAccessParameter().getSorter().toString()));
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "PAGEINDEX", this.getAccessParameter().pageIndex, Types.INTEGER));
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "PAGESIZE", this.getAccessParameter().pageSize, Types.INTEGER));
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "TOTALCOUNT", this.getAccessParameter().pageCount, Types.INTEGER, DBParameterDirection.Output));

                list = SqlHelper.queryForListBySp("SEARCH", dbParmCollection);
            }
            else{
                String sqlText ="SELECT * FROM "+selectTable +" WHERE "+this.getAccessParameter().getFilter().toString() ;
                list = SqlHelper.queryForList(CommandType.Text,sqlText);
            }
			if (!list.isEmpty()) {
			    entity = FillEntityHelper.fillEntity((Map<String, Object>) list.get(0), type, "");
			}

		}
		//this.getAccessParameter().getFilter().cleanFilter();//可以不需要如此做了， 查询参数已改为线程安全
		return entity;
	}

    /**
     * 通用的搜索方法，根据传入的数据类型返回实体对象的集合。
     *
     * @param type 搜索的实体对象的类型
     * @return 查找到的实体对象集合
     * @throws Exception
     */
    public EntityCollection searchEntityCollection(Type type) throws Exception {

        EntityCollection collection = new EntityCollection();
        List<Map<String, Object>> list = null;

        if (Class.forName(type.getTypeName()).isAnnotationPresent(EntityAttribute.class)) {
            EntityAttribute attribute = (EntityAttribute) Class.forName(type.getTypeName())
                .getAnnotation(EntityAttribute.class);
            String selectTable = attribute.selectTable();
            String identityProperty = //attribute.identityProperty();
                //此写法增强了此注解的灵活性 可以为数据库字段的名称 也可以为实体模型属性名称
                CUtils.camelToUnderline(
                    ReflectionUtils.getPropertyName(Class.forName(type.getTypeName()), CUtils.underlineToCamel(attribute.identityProperty())));

            boolean isExitProcedure = SqlHelper.isExitProcedure("SEARCH");
            if (isExitProcedure) {
                DBParameterCollection dbParmCollection = new DBParameterCollection();
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "SOURCETABLE", selectTable));
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "PRIMARYKEY", identityProperty));
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "WHERECLAUSE", this.getAccessParameter().getFilter().toString()));
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "ORDERBYCLAUSE", this.getAccessParameter().getSorter().toString()));
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "PAGEINDEX", this.getAccessParameter().pageIndex, Types.INTEGER));
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "PAGESIZE", this.getAccessParameter().pageSize, Types.INTEGER));
                DBParameter totalCountParm = new DBParameter(SqlHelper.getDbParamterPrefix() + "TOTALCOUNT", 0, Types.INTEGER, DBParameterDirection.Output);
                dbParmCollection.add(totalCountParm);


                list = SqlHelper.queryForListBySp("Search", dbParmCollection);

                if (tlTotalCount.get() == null || tlTotalCount.get() == 0) {
                    tlTotalCount.set(totalCountParm.getParameterValue() == null ? 0
                        : new Integer(String.valueOf(totalCountParm.getParameterValue())));
                }
                //this.totalCount = tlTotalCount.get().intValue();
                this.getAccessParameter().setTotalCount(tlTotalCount.get().intValue());
            } else {//如果找不到对应的存储过程 则需要先查询返回对应的主键列表集合对其分页取数,再通过取得分页后的主键列表集合关联向后台取数
                list = getListWithPaged(selectTable, identityProperty);
            }

            for (Map<String, Object> map : list) {
                Entity entity = FillEntityHelper.fillEntity(map, type, "");
                if (entity != null)
                    collection.add(entity);
            }

        }

        //this.getAccessParameter().getFilter().cleanFilter();
        return collection;
    }

    private List<Map<String, Object>> getListWithPaged(String selectTable, String identityProperty) throws Exception {
        List<Map<String, Object>> list = null;
        /**1.根据条件向后台查询返回主键集合列表**/
        String sql = "SELECT " + identityProperty +" FROM " + selectTable +" WHERE " + this.getAccessParameter().filter.toString()+" ORDER BY "+ (this.getAccessParameter().sorter.toString().length() < 1 ? identityProperty : this.getAccessParameter().sorter.toString()) ;

        list = SqlHelper.queryForList(CommandType.Text,sql);
        List pageList = CUtils.getPagedList(this.getAccessParameter().pageIndex,this.getAccessParameter().pageSize,list);

        List<String> keyList = new ArrayList<>();
//        for(Map<String, Object> map : (List<Map<String, Object>>)pageList){
//            for(Object v : map.values()){
//                keyList.add(v.toString());
//            }
//        }
        //改用java8方式去遍历
        ((List<Map<String, Object>>)pageList).stream().forEach(map->map.forEach((k,v)-> keyList.add(v.toString())));

        this.getAccessParameter().setTotalCount(list.size());

        /**2.通过取得分页后的主键列表集合关联向后台取数**/
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT * FROM " + selectTable +" WHERE "+identityProperty +" IN (");
        for(int i = 0;i<keyList.size();i++){
            if(i != keyList.size()-1)
                sb.append("? , ");
            else
                sb.append("?");
        }
        sb.append(")");

        list = SqlHelper.queryForList(CommandType.Text,sb.toString(),keyList.toArray());

        return list;
    }


    /**
	 * 通用的搜索方法，根据传入的类型返回实体对象的集合。
	 * @param type 搜索的实体对象的类型
	 * @return 查找到的实体对象集合
	 * @throws Exception
	 */
    public List<? extends Entity> searchEntityList(Type type) throws Exception {
        List collection = new ArrayList();
        List<Map<String, Object>> list = null;

        if (Class.forName(type.getTypeName()).isAnnotationPresent(EntityAttribute.class)) {
            EntityAttribute attribute = (EntityAttribute) Class.forName(type.getTypeName())
                .getAnnotation(EntityAttribute.class);
            String selectTable = attribute.selectTable();
            String identityProperty = //attribute.identityProperty();
                //此写法增强了此注解的灵活性 可以为数据库字段的名称 也可以为实体模型属性名称
                CUtils.camelToUnderline(
                    ReflectionUtils.getPropertyName(Class.forName(type.getTypeName()), CUtils.underlineToCamel(attribute.identityProperty())));
            boolean isExitProcedure = SqlHelper.isExitProcedure("SEARCH");
            if (isExitProcedure) {
                DBParameterCollection dbParmCollection = new DBParameterCollection();
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "SOURCETABLE", selectTable));
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "PRIMARYKEY", identityProperty));
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "WHERECLAUSE", this.getAccessParameter().getFilter().toString()));
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "ORDERBYCLAUSE", this.getAccessParameter().getSorter().toString()));
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "PAGEINDEX", this.getAccessParameter().pageIndex, Types.INTEGER));
                dbParmCollection.add(new DBParameter(SqlHelper.getDbParamterPrefix() + "PAGESIZE", this.getAccessParameter().pageSize, Types.INTEGER));

                DBParameter totalCountParm = new DBParameter(SqlHelper.getDbParamterPrefix() + "TOTALCOUNT", 0, Types.INTEGER, DBParameterDirection.Output);
                dbParmCollection.add(totalCountParm);
                System.out.println(dbParmCollection);

                list = SqlHelper.queryForListBySp("Search", dbParmCollection);

                if (tlTotalCount.get() == null || tlTotalCount.get() == 0) {
                    tlTotalCount.set(totalCountParm.getParameterValue() == null ? 0
                        : new Integer(String.valueOf(totalCountParm.getParameterValue())));
                }
                this.getAccessParameter().setTotalCount(tlTotalCount.get().intValue()) ;

            } else {//如果找不到对应的存储过程 则需要先查询返回对应的主键列表集合对其分页取数,再通过取得分页后的主键列表集合关联向后台取数
                list = getListWithPaged(selectTable, identityProperty);
            }

            for (Map<String, Object> map : list) {
                Entity entity = FillEntityHelper.fillEntity(map, type, "");
                if (entity != null)
                    collection.add(entity);
            }

        }
        logger.info("searchEntityList:" + type.getTypeName());
        return collection;
    }

	/**
	 * 通用的搜索方法，根据传入的数据类型返回实体对象的分页对象。
	 * @param type 搜索的实体对象的类型
	 * @return 查找到的实体对应的分页对象
	 * @throws ClassNotFoundException
	 */
	public <T> PageInfo<T> searchEntityPageInfo(Type type) throws Exception{
		List<T> list = (List<T>) searchEntityList(type);
		PageInfo<T> pageInfo = new PageInfo<T>(this.getAccessParameter().getPageSize(),this.getAccessParameter().getPageIndex(),this.getAccessParameter().getTotalCount(),this.getAccessParameter().getPageCount(),list);

		return pageInfo;
	}

    /**
     * 通过sql查询语句或执行存储过程,返回对应实体集合 [支持递归]
     * @param type 实体类型
     * @param commandType 执行类型[SQL语句或存储过程]
     * @param cmdText SQL语句或存储过程名称
     * @param objects 执行参数
     * @return  对应实体集合
     * @throws Exception
     */
	public  List<? extends Entity> searchEntityListBySQL(Type type,CommandType commandType,String cmdText,Object...objects) throws Exception{
		//T entity = type.newInstance();	
		List collection = new ArrayList();
		if(commandType.equals(CommandType.Text))
			cmdText = handleAddMustColunmWhenQueryWithSql(type,cmdText);
		
		DBTablesCollection tables = SqlHelper.queryForTablesCollection(commandType,cmdText,objects);
		if (null == tables || tables.isEmpty())
			return null;
		DBTable table = (DBTable)tables.get(0);

		List<Map<String, Object>> list = table.getData();
		for (Map<String, Object> map : list) {
			Entity entity = FillEntityHelper.fillEntity(map, type, "");
			if (entity != null)
				collection.add(entity);
		}

		return collection;
	}
	
	/**
	 * 处理不以"SELECT * FROM "查询时,增加主查询语句中必须增加的主键和关联字段
	 * @param type  type 实体类型
	 * @param cmdText 原查询sql语句
	 * @return 处理后的查询sql语句
	 * @throws Exception
	 */
	private String handleAddMustColunmWhenQueryWithSql(Type type,String cmdText) throws Exception {
		StringBuffer sb = new StringBuffer();
		StringBuffer selectSb = new StringBuffer();
		HashSet<String> selectColSet = new HashSet<String>();
		String[] cmd = cmdText.split(";");
		String selectColumnsBefore ="",selectColumnsAfter ="";
		String masterSqlCmd = cmd[0].toUpperCase().trim();
		if(!masterSqlCmd.contains("*")) {
			selectColumnsBefore = masterSqlCmd.substring(masterSqlCmd.indexOf("SELECT")+6, masterSqlCmd.indexOf("FROM"));
			selectColumnsAfter = selectColumnsBefore.trim() +","+addMustColunmWhenQueryWithSql(type);
			for(String str : selectColumnsAfter.split(",")) {//去除重复
				selectColSet.add(str);
			}
			for(String colName : selectColSet) {
				selectSb.append(colName).append(",");
			}
			selectColumnsAfter = selectSb.length() > 0 ? " " + selectSb.deleteCharAt(selectSb.length()-1).toString() +" ": " ";
			masterSqlCmd = "SELECT" + selectColumnsAfter.toUpperCase()+ masterSqlCmd.substring(masterSqlCmd.indexOf("FROM"),masterSqlCmd.length());
		}
		sb.append(masterSqlCmd).append(";");
		if(cmd.length > 1) {
			for(int i = 1 ;i < cmd.length;i++) {
				sb.append(cmd[i]).append(";");
			}
		}
		return sb.toString();
	}
	/**
	 * 在用sql查询语句查询实体对象时，为避免不是以"select * from"查询时,而实际又要关联查询成员实体或实体集合<br/>
	 * 需要增加必须关联的查询字段,如此以来才可以保证递归查询的结果
	 * @param type 实体类型
	 * @return
	 * @throws Exception
	 */
	private String addMustColunmWhenQueryWithSql(Type type) throws Exception {		
		HashSet<String> mustSelectColSet = new HashSet<String>();
		Field[] fields = Class.forName(type.getTypeName()).getDeclaredFields();
		String propertyName,columnName,referredKeyColumnName;
		StringBuffer sb = new StringBuffer();
		//增加主键查询字段
		if (Class.forName(type.getTypeName()).isAnnotationPresent(EntityAttribute.class)) {
			EntityAttribute attribute = (EntityAttribute) Class.forName(type.getTypeName())
					.getAnnotation(EntityAttribute.class);
			//此写法增强了此注解的灵活性 可以为数据库字段的名称 也可以为实体模型属性名称	
			String identityColumnName=
					CUtils.camelToUnderline(
							ReflectionUtils.getPropertyName(Class.forName(type.getTypeName()),CUtils.underlineToCamel(attribute.identityProperty())));
			if(null != identityColumnName && identityColumnName.length() >0)
				mustSelectColSet.add(identityColumnName);
		}
		//增加关联查询字段
		for (Field field : fields) { // 遍历所有属性	
			field.setAccessible(true);
			//递归查询子实体对象时,必须要关联的字段
			if(field.getType().getSuperclass() == Entity.class) {//如果属性是另外类型的单个实体
				EntitySubClassAttribute subAttribute = field.getAnnotation(EntitySubClassAttribute.class);
				if (subAttribute != null && subAttribute.parentKeyProperty() != null) {
					propertyName = subAttribute.parentKeyProperty();
					Field f = type.getClass().getField(propertyName);
					if(f.isAnnotationPresent(EntityFieldAttribute.class)) {
						EntityFieldAttribute fieldAttr = f.getAnnotation(EntityFieldAttribute.class);
						columnName = fieldAttr.columnName();
						if(columnName != null && columnName.length() >0)
							mustSelectColSet.add(columnName);
						else
							mustSelectColSet.add(CUtils.camelToUnderline(propertyName));
					}
					else
						mustSelectColSet.add(CUtils.camelToUnderline(propertyName));
				}
			}
			else if (field.getType() == EntityCollection.class || (Collection.class.isAssignableFrom(field.getType())
					&& Entity.class.isAssignableFrom(ReflectionUtils.getFieldClass(field)))) {// 如果属性是另外类型实体的集合
				if (field.getType().isAnnotationPresent(EntityCollectionAttribute.class)) {
					EntityCollectionAttribute collectionAttr = field.getType().getAnnotation(EntityCollectionAttribute.class);
					referredKeyColumnName = CUtils
							.camelToUnderline(ReflectionUtils.getPropertyName(Class.forName(type.getTypeName()),
									CUtils.underlineToCamel(collectionAttr.referredKeyProperty())));
					if(null != referredKeyColumnName && referredKeyColumnName.length() >0)
						mustSelectColSet.add(referredKeyColumnName);
				}
			}				
		}

		for(String colName : mustSelectColSet) {
			sb.append(colName).append(",");
		}
		
		return sb.length() > 0 ? sb.deleteCharAt(sb.length()-1).toString() : "";
	}


    /**
     * 批量查询,通过一组ids批量查询到对应的实体集合列表
     * @param type  实体类型
     * @param ids   主键列表
     * @return 实体集合列表
     * @throws Exception
     */
    public List<? extends Entity> searchEntityListByIds(Type type, List<? extends Serializable> ids) throws Exception{
        List collection = new ArrayList();
        if (Class.forName(type.getTypeName()).isAnnotationPresent(EntityAttribute.class)) {
            EntityAttribute attribute = (EntityAttribute) Class.forName(type.getTypeName())
                .getAnnotation(EntityAttribute.class);
            String tableName = attribute.selectTable();
            String identityColumnName=
                CUtils.camelToUnderline(
                    ReflectionUtils.getPropertyName(Class.forName(type.getTypeName()),CUtils.underlineToCamel(attribute.identityProperty())));
            StringBuffer sb = new StringBuffer();
            if(tableName.length() > 0 && null != identityColumnName && identityColumnName.length() >0 && ids.size() >0){
                sb.append("SELECT * FROM " +tableName +" WHERE "+identityColumnName +" IN (");
                for(int i = 0;i<ids.size();i++){
                    if(i != ids.size()-1)
                        sb.append("? , ");
                    else
                        sb.append("?");
                }
                sb.append(")");
            }
            collection = searchEntityListBySQL(type,CommandType.Text,sb.toString(),ids.toArray());
        }

        return collection;
    }

	/**
	 * 删除实体对象的主方法（支持级联删除） 并提供事务处理机制
	 * @param entity 实体对象
	 * @return  删除成功返回true 并提交事务，否则返回false 回滚事务
	 * @throws SQLException
	 */
	public boolean delete(Entity entity) throws SQLException{
		boolean result = false;		
		try
		{
			SqlHelper.beginTransaction();
			result = deleteWithRecursive(entity);
			if(result)
				SqlHelper.commitTransaction();
			else
				SqlHelper.rollBackTransaction();
		}
		catch(Exception e){
			SqlHelper.rollBackTransaction();
		}
		
		return result;	
	}
	
	/**
	 * 在数据库中级联删除对应的实体<BR/>
	 * 通用的删除实体对象。此方法会先检测该实体对象中设置的级联删除对象，在删除该实体对象前先删除对应设置的级联对象
	 * @param entity 实体对象
	 * @return 删除成功返回true，否则返回false
	 * @throws Exception
	 */
	public boolean deleteWithRecursive(Entity entity) throws Exception {
		if (entity == null)
			return false;

		if (Class.forName(entity.getClass().getTypeName()).isAnnotationPresent(EntityAttribute.class)) {
			EntityAttribute attribute = (EntityAttribute) Class.forName(entity.getClass().getTypeName())
					.getAnnotation(EntityAttribute.class);
			
			Class[] cascadeList = attribute.cascadeDeleteEntitys();
			
			if (cascadeList != null && cascadeList.length > 0) {// 如果该实体设置了对应的级联删除对象,则先删除其级联实体对象
				boolean deleteSingleResult = false;//记录级联删除单实体 执行结果状态（成功or失败）
				Field[] fieldList = entity.getClass().getDeclaredFields();
				for (Field field : fieldList) {
					field.setAccessible(true);
					if (field.getType().getSuperclass() == Entity.class) {// 如果属性是另外类型的单个实体
																			// 考虑到这种形式的实体对象是由数据库表反向生成的（天生就具有一一对应的关系）这样就无需考虑多重继承问题
																			// 如此以来也便于规范和统一
						boolean isIncascade = this.classIncascadeList(field.getType(), cascadeList);
						Object obj = ReflectionUtils.getFieldValue(entity, field.getName());
						if (isIncascade && obj != null) {
							deleteSingleResult = this.deleteWithRecursive((Entity) obj);// 递归删除
							if(!deleteSingleResult)
								return false;
						}
					} else if (field.getType() == EntityCollection.class) {/* 如果属性是另外类型的实体集合 */
						EntityCollectionAttribute collectionAttribute = (EntityCollectionAttribute) field
								.getAnnotation(EntityCollectionAttribute.class);
						Class subEntity = collectionAttribute.elementType();
						boolean isIncascade = this.classIncascadeList(subEntity, cascadeList);

						EntityCollection collection = (EntityCollection) ReflectionUtils.getFieldValue(entity,
								field.getName());
						if (!collection.isEmpty() && isIncascade) {
							Iterator item = collection.iterator();
							int i = 0;
							while (item.hasNext()) {
								Entity itemEntity = (Entity) item.next();
								deleteSingleResult = this.deleteWithRecursive(itemEntity);// 递归删除
								if(!deleteSingleResult)
									return false;
							}
						}
					} else if (Collection.class.isAssignableFrom(field.getType())
							&& Entity.class.isAssignableFrom(ReflectionUtils.getFieldClass(field))) { //如果属性是另外类型实体的泛型List集合 例：List<Entity>
						Class subEntity = null;
						if (field.getType().isAnnotationPresent(EntityCollectionAttribute.class)) {
							EntityCollectionAttribute collectionAttribute = (EntityCollectionAttribute) field
									.getAnnotation(EntityCollectionAttribute.class);
							subEntity = collectionAttribute.elementType();
						} else {
							subEntity = ReflectionUtils.getFieldClass(field);
						}
						boolean isIncascade = this.classIncascadeList(subEntity, cascadeList);

						List<?> list = (List<?>) ReflectionUtils.getFieldValue(entity, field.getName());
						if (!list.isEmpty() && isIncascade) {
							Iterator item = list.iterator();
							while (item.hasNext()) {
								Entity itemEntity = (Entity) item.next();
								deleteSingleResult = this.deleteWithRecursive(itemEntity);// 递归删除
								if(!deleteSingleResult)
									return false;
							}
						}
					}
				}
			}
			
			return this.deleteForSingle(entity);
		}
		return false;
	}

	/**
	 * 删除单个实体对象 不考虑级联删除对象
	 * @param entity 实体对象
	 * @return  删除成功返回true，否则返回false
	 * @throws Exception
	 */
	public boolean deleteForSingle(Entity entity) throws Exception {	
		if (entity == null)
			return false;
		if (Class.forName(entity.getClass().getTypeName()).isAnnotationPresent(EntityAttribute.class)) {
			EntityAttribute attribute = Class.forName(entity.getClass().getTypeName())
					.getAnnotation(EntityAttribute.class);
			String deleteTable = attribute.updateTable();
			String identityProperty = ReflectionUtils.getPropertyName(entity.getClass(),CUtils.underlineToCamel(
					attribute.identityProperty()));/*
													 * entity实体中定义的关键字属性
													 * （例：private String id;）
													 */
			String procedureName = "SP_" + deleteTable.toUpperCase() + "_DELETE";
			boolean isExitProcedure = SqlHelper.isExitProcedure(procedureName);

			String identityValue = (String) ReflectionUtils.getFieldValue(entity, identityProperty);
			int i ;
			if (isExitProcedure) {
				 i = SqlHelper.excuteNonQuery(CommandType.StoredProcedure, procedureName,identityValue);
				 if(i > 0)
					 return true;
			}
			else{
				 i = SqlHelper.excuteNonQuery(CommandType.Text,
						"DELETE FROM " + deleteTable + " WHERE " + CUtils.camelToUnderline(identityProperty) + " =? ",
						identityValue);
				 if(i > 0)
					 return true;
			}		
		}
		
		return false;		
	}
	
	/**
	 * 单纯的根据id的值从数据库中删除一个单一实体对象
	 * @param type 实体对象的类型
	 * @param idValue 实体对象ID 的值
	 * @return 返回成功或失败
	 * @throws ClassNotFoundException
	 */
	public boolean deleteByIdForSingle(Type type, Object idValue) throws ClassNotFoundException {
		int i = -1;
		if (Class.forName(type.getTypeName()).isAnnotationPresent(EntityAttribute.class)) {
			EntityAttribute attribute = (EntityAttribute) Class.forName(type.getTypeName())
					.getAnnotation(EntityAttribute.class);
			String deleteTable = attribute.updateTable();
			String id = CUtils.camelToUnderline(
					ReflectionUtils.getPropertyName(Class.forName(type.getTypeName()),CUtils.underlineToCamel(attribute.identityProperty())));
			try {
				i = SqlHelper.excuteNonQuery(CommandType.Text,
						"DELETE FROM " + deleteTable + " WHERE " + id + " =? ", idValue);
				return true;
			} catch (Exception e) {
				return false;
			}
		}
		return false;
	}

    /**
     *批量删除实体对象集合，支持递归删除
     * @param type 实体类型
     * @param ids  实体对应的主键集合列表
     * @param recursive 是否递归删除
     * @return  返回成功或失败
     * @throws Exception
     */
	public boolean deleteBatchIds(Class type,List<? extends Serializable> ids,boolean recursive) throws Exception{
        int result = -1;
        boolean recursiveResult = true;
        if (Class.forName(type.getTypeName()).isAnnotationPresent(EntityAttribute.class)) {
            EntityAttribute attribute = (EntityAttribute) Class.forName(type.getTypeName())
                .getAnnotation(EntityAttribute.class);
            String tableName = attribute.updateTable();
            String identityColumnName =
                CUtils.camelToUnderline(
                    ReflectionUtils.getPropertyName(Class.forName(type.getTypeName()), CUtils.underlineToCamel(attribute.identityProperty())));
            StringBuffer sb = new StringBuffer();
            if(recursive){
                List<Entity> list = (List<Entity>) searchEntityListByIds(type,ids);
                for (Entity entity : list){
                    recursiveResult = deleteWithRecursive(entity);
                    if(!recursiveResult)
                        break;
                }
                return recursiveResult;
            }
            else {
                if(tableName.length() > 0 && null != identityColumnName && identityColumnName.length() >0 && ids.size() >0){
                    sb.append("DELETE FROM " +tableName +" WHERE "+identityColumnName +" IN (");
                    for(int i = 0;i<ids.size();i++){
                        if(i != ids.size()-1)
                            sb.append("? , ");
                        else
                            sb.append("?");
                    }
                    sb.append(")");
                    result = SqlHelper.excuteNonQuery(CommandType.Text,sb.toString(),ids.toArray());
                }
                return (result>=0);
            }
        }

        return false;
    }


	/**
	 * 判断级联删除/更新类中是否设置了指定的实体类
	 * @param clazz 指定的实体类
	 * @param clazzs 商业实体中设置的级联删除/更新类
	 * @return 存在：true 不存在：false
	 */
	private boolean classIncascadeList(Class clazz, Class[] clazzs) {
		boolean isIncascade = false;
		for (Class cz : clazzs) {
			if (cz.getTypeName().equals(clazz.getTypeName())) {
				isIncascade = true;
				break;
			}
		}
		return isIncascade;
	}

	/**
	 * 新增加实体对象到数据库的主方法 提供事务处理 机制
	 * @param entity 实体对象
	 * @return  增加成功返回true 并提交事务，否则返回false 回滚事务
	 * @throws SQLException
	 */
	public boolean insert(Entity entity) throws SQLException{
		boolean result = false;		
		try
		{
			SqlHelper.beginTransaction();
			result = insertWithRecursive(entity);
			if(result)
				SqlHelper.commitTransaction();
			else
				SqlHelper.rollBackTransaction();
		}
		catch(Exception e){
			e.printStackTrace();
			SqlHelper.rollBackTransaction();
		}
		
		return result;	
	}
	/**
	 * 在数据库中级联增加对应的实体
	 * @param entity 实体对象
	 * @return 增加成功返回true，否则返回false
	 * @throws Exception
	 */
	public boolean insertWithRecursive(Entity entity) throws Exception {
		if (Class.forName(entity.getClass().getTypeName()).isAnnotationPresent(EntityAttribute.class)) {
			EntityAttribute attribute = (EntityAttribute) Class.forName(entity.getClass().getTypeName())
					.getAnnotation(EntityAttribute.class);
			Class[] cascadeList = attribute.cascadeUpdateEntitys();

			try {
				boolean insert = this.insertForSingle(entity);
				if(!insert)
					return false;
				if (cascadeList.length > 0) {
					Field[] fieldList = entity.getClass().getDeclaredFields();
					for (Field field : fieldList) {
						field.setAccessible(true);
						if (field.getType().getSuperclass() == Entity.class) {// 如果属性是另外类型的单个实体
							boolean isIncascade = this.classIncascadeList(field.getType(), cascadeList);
							Object obj = ReflectionUtils.getFieldValue(entity, field.getName());
							if (isIncascade && obj != null) {
								insert = this.insertWithRecursive((Entity) obj);// 递归插入
								//如果有一条未执行成功直接返回 false
								if(!insert)
									break;
							}
						} else if (field.getType() == EntityCollection.class) {/* 如果属性是另外类型的实体集合 */
							EntityCollectionAttribute collectionAttribute = (EntityCollectionAttribute) field
									.getAnnotation(EntityCollectionAttribute.class);
							Class subEntity = collectionAttribute.elementType();
							boolean isIncascade = this.classIncascadeList(subEntity, cascadeList);

							EntityCollection collection = (EntityCollection) ReflectionUtils.getFieldValue(entity,
									field.getName());
							if (!collection.isEmpty() && isIncascade) {
								Iterator item = collection.iterator();
								int i = 0;
								while (item.hasNext()) {
									Entity itemEntity = (Entity) item.next();
									insert = this.insertWithRecursive(itemEntity);// 递归插入
									//如果有一条未执行成功直接返回 false
									if(!insert)
										break;
								}
								if(!insert)
									break;
							}
						} else if (Collection.class.isAssignableFrom(field.getType())
								&& Entity.class.isAssignableFrom(ReflectionUtils.getFieldClass(field))) { // 如果属性是另外类型实体的泛型List集合
																											// 例：LIst<Entity>
							Class subEntity = null;
							if (field.getType().isAnnotationPresent(EntityCollectionAttribute.class)) {
								EntityCollectionAttribute collectionAttribute = (EntityCollectionAttribute) field
										.getAnnotation(EntityCollectionAttribute.class);
								subEntity = collectionAttribute.elementType();
							} else {
								subEntity = ReflectionUtils.getFieldClass(field);
							}
							boolean isIncascade = this.classIncascadeList(subEntity, cascadeList);

							List<?> list = (List<?>) ReflectionUtils.getFieldValue(entity, field.getName());
							if (!list.isEmpty() && isIncascade) {
								Iterator item = list.iterator();
								while (item.hasNext()) {
									Entity itemEntity = (Entity) item.next();
									insert = this.insertWithRecursive(itemEntity);// 递归插入
									//如果有一条未执行成功直接返回 false
									if(!insert)
										break;
								}
								if(!insert)//跳出外层实体遍历其属性 减少不必要的开销
									break;
							}
						}
					}
				}
				return insert;
			} catch (Exception e) {
				e.printStackTrace();
				return false;
			}
		}
		return false;
	}
	
	/**
	 * 向数据库中新增单个实体对象数据
	 * @param entity 实体对象
	 * @return 新增成功返回 true 新增失败返回 false 
	 * @throws Exception
	 */
	public boolean insertForSingle(Entity entity) throws Exception {
		if (entity == null)
			return false;
		if (Class.forName(entity.getClass().getTypeName()).isAnnotationPresent(EntityAttribute.class)) {
			EntityAttribute attribute = (EntityAttribute) Class.forName(entity.getClass().getTypeName())
					.getAnnotation(EntityAttribute.class);
			String updateTable = attribute.updateTable();
			String identityProperty = ReflectionUtils.getPropertyName(entity.getClass(),
					CUtils.underlineToCamel(attribute.identityProperty()));
			String procedureName = "SP_" + updateTable.toUpperCase() + "_INSERT";
			boolean useGeneratedKeys = attribute.useGeneratedKeys();

			if (SqlHelper.isExitProcedure(procedureName)) {/* 如果数据库中存在对应的存储过程 */
				DBParameterCollection params = SqlHelper.prepareParameters(procedureName, entity); // this.prepareParameters(procedureName,
																									// entity,"V_");
				try {
					int i = SqlHelper.executeNonQuery(procedureName, params);
					if(i==-1)
						return false;
					if (useGeneratedKeys) {// 如果要返回实体对象的主键自增id
						for (int p = 0; p < params.size(); p++) {
							DBParameter param = (DBParameter) params.get(p);
							if (identityProperty.equalsIgnoreCase(param.getParameterName())) {
								Object id = param.getParameterValue();
								ReflectionUtils.setFieldValue(entity, identityProperty, new Integer(String.valueOf(id)));
							}
						}
					}
					return true;
				} catch (Exception e) {
					e.printStackTrace();
					return false;
				}				
			} else {
				String colName = "", insertColNames = "", insertValues = "", commandText = "";
				Object colValue = null;
				boolean colExit = true;
				StringBuilder sbInsertColNames = new StringBuilder();
				StringBuilder sbInsertValues = new StringBuilder();
				ArrayList insertValuesList = new ArrayList();
				Field[] fields = entity.getClass().getDeclaredFields();

				try {
					for (Field field : fields) {
						field.setAccessible(true);
						
						if(ReflectionUtils.isStaticFinalField(field))//如果该字段使用了static final 修饰 【通常这么修饰的字段是用来定义常量的】
							continue;//	遍历下一个
						
						colName = "";
                        colExit = true;
						if (field.isAnnotationPresent(EntityFieldAttribute.class)) {// 如果属性有标记在数据库中表对应的字段
							EntityFieldAttribute fieldAttribute = (EntityFieldAttribute) field
									.getAnnotation(EntityFieldAttribute.class);
							colName = fieldAttribute.columnName();
                            colExit = fieldAttribute.exist();
						}
						if (colName.equals("")) {
							colName = CUtils.camelToUnderline(field.getName());
						}
						if(!colExit)//如果该属性标记了在数据库中不存在
                            continue;
                        colValue = ReflectionUtils.getFieldValue(entity, field.getName());
						if (colValue == null || "".equals(colValue) )
							continue;

						if (field.getType() == String.class) {
							/*field.getType().equals(String.class) 这样也行，但是前面的好，因为同一个类型的字节码就一份.*/									 
							sbInsertColNames.append(colName + " ,");
                            sbInsertValues.append("? ,");
                            insertValuesList.add((String)colValue);
						} else if (field.getType() == Date.class) {
							sbInsertColNames.append(colName + " ,");
                            sbInsertValues.append("? ,");
                            insertValuesList.add(DateUtils.format((Date) colValue));
						} else if (field.getType() == boolean.class || field.getType() == Boolean.class) {
							sbInsertColNames.append(colName + " ,");
                            sbInsertValues.append("? ,");
                            insertValuesList.add(((Boolean) colValue) ? 1 : 0);
						} else if (field.getType().getSuperclass() == Entity.class) {/*如果属性是另外类型的单个实体*/
							
						} else if (field.getType() == EntityCollection.class) {/* 如果属性是另外类型的实体集合 */

						} else if (Collection.class.isAssignableFrom(field.getType())) {/*如果属性是另外类型的List集合 */
							
						}
						else {
							sbInsertColNames.append(colName + " ,");
                            sbInsertValues.append("? ,");
                            insertValuesList.add(colValue);
						}					
					}
					
					insertColNames = sbInsertColNames.toString();
					insertValues = sbInsertValues.toString();
					if (insertColNames.endsWith(","))
						insertColNames = insertColNames.substring(0, insertColNames.length() - 1);
					if (insertValues.endsWith(","))
						insertValues = insertValues.substring(0, insertValues.length() - 1);
					commandText = "INSERT INTO " + updateTable + "(" + insertColNames + ") VALUES (" + insertValues + ")";
					
					if (useGeneratedKeys) {
						Object id = SqlHelper.insertAndGetGeneratedId(commandText,insertValuesList.toArray());
						if(id==null)
							return false;
						ReflectionUtils.setFieldValue(entity, identityProperty, new Integer(String.valueOf(id)));						
					} else {
						int i = SqlHelper.excuteNonQuery(CommandType.Text, commandText,insertValuesList.toArray());
						if(i==-1)
							return false;
					}
					return true;
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return false;
				}
			}			
		}
		return false;
	}

	/**
	 * 更新实体对象到数据库的主方法 提供事务处理机制
	 * @param entity 实体对象
	 * @return  增加成功返回true 并提交事务，否则返回false 回滚事务
	 * @throws SQLException
	 */
	public boolean update(Entity entity) throws SQLException{
		boolean result = false;		
		try
		{
			SqlHelper.beginTransaction();
			result = updateWithRecursive(entity);
			if(result)
				SqlHelper.commitTransaction();
			else
				SqlHelper.rollBackTransaction();
		}
		catch(Exception e){
			e.printStackTrace();
			SqlHelper.rollBackTransaction();
		}
		
		return result;	
	}

    /**
     * 更新实体对象到数据库 提供事务处理机制<br/>
     * 选择性字段更新,只对值不为空的字段更新
     * @param entity 实体对象
     * @return 增加成功返回true 并提交事务，否则返回false 回滚事务
     * @throws SQLException
     */
    public boolean updateWithSelective(Entity entity) throws SQLException{
        boolean result = false;
        try
        {
            SqlHelper.beginTransaction();
            result = updateWithRecursive(entity,true);
            if(result)
                SqlHelper.commitTransaction();
            else
                SqlHelper.rollBackTransaction();
        }
        catch(Exception e){
            e.printStackTrace();
            SqlHelper.rollBackTransaction();
        }

        return result;
    }


    /**
     * 在数据库中级联更新对应的实体 递归更新对应实体<br/>
     * 先更新主实体 后更新从实体
     * @param entity 实体对象
     * @return
     * @throws Exception
     */
    public boolean updateWithRecursive(Entity entity) throws Exception {
        return this.updateWithRecursive(entity,false);
    }
	
	/**
	 * 在数据库中级联更新对应的实体 递归更新对应实体
	 * 先更新主实体 后更新从实体
	 * @param entity 实体对象
     * @param  isSelectiveUpdate  是否选择性字段更新（即：只更新指定不为空的字段）
	 * @return 更新成功返回true，否则返回false
	 * @throws Exception
	 */
	public boolean updateWithRecursive(Entity entity,boolean isSelectiveUpdate) throws Exception {
		if (Class.forName(entity.getClass().getTypeName()).isAnnotationPresent(EntityAttribute.class)) {
			EntityAttribute attribute = (EntityAttribute) Class.forName(entity.getClass().getTypeName())
					.getAnnotation(EntityAttribute.class);
			Class[] cascadeList = attribute.cascadeUpdateEntitys();

			try {
				boolean update = this.updateForSingle(entity,isSelectiveUpdate);
				if(!update)
					return false;
				if (cascadeList.length > 0) {
					Field[] fieldList = entity.getClass().getDeclaredFields();
					for (Field field : fieldList) {
						field.setAccessible(true);
						if (field.getType().getSuperclass() == Entity.class) {// 如果属性是另外类型的单个实体
							boolean isIncascade = this.classIncascadeList(field.getType(), cascadeList);
							Object obj = ReflectionUtils.getFieldValue(entity, field.getName());
							if (isIncascade && obj != null) {
								this.updateWithRecursive((Entity) obj);// 递归更新
							}
						} else if (field.getType() == EntityCollection.class) {/* 如果属性是另外类型的实体集合 */
							EntityCollectionAttribute collectionAttribute = (EntityCollectionAttribute) field
									.getAnnotation(EntityCollectionAttribute.class);
							Class subEntity = collectionAttribute.elementType();
							boolean isIncascade = this.classIncascadeList(subEntity, cascadeList);

							EntityCollection collection = (EntityCollection) ReflectionUtils.getFieldValue(entity,
									field.getName());
							if (!collection.isEmpty() && isIncascade) {
								Iterator item = collection.iterator();
								int i = 0;
								while (item.hasNext()) {
									Entity itemEntity = (Entity) item.next();
									this.updateWithRecursive(itemEntity);// 递归更新
								}
							}
						} else if (Collection.class.isAssignableFrom(field.getType())
								&& Entity.class.isAssignableFrom(ReflectionUtils.getFieldClass(field))) { // 如果属性是另外类型实体的泛型List集合
																											// 例：LIst<Entity>
							Class subEntity = null;
							if (field.getType().isAnnotationPresent(EntityCollectionAttribute.class)) {
								EntityCollectionAttribute collectionAttribute = (EntityCollectionAttribute) field
										.getAnnotation(EntityCollectionAttribute.class);
								subEntity = collectionAttribute.elementType();
							} else {
								subEntity = ReflectionUtils.getFieldClass(field);
							}
							boolean isIncascade = this.classIncascadeList(subEntity, cascadeList);

							List<?> list = (List<?>) ReflectionUtils.getFieldValue(entity, field.getName());
							boolean result = false;
							if (!list.isEmpty() && isIncascade) {
								Iterator item = list.iterator();
								while (item.hasNext()) {
									Entity itemEntity = (Entity) item.next();
									result = this.updateWithRecursive(itemEntity);// 递归更新
									if(!result){
										break;										
									}
								}
							}
						}
					}
				}
				return update;
			} catch (Exception e) {
				e.printStackTrace();
				return false;
			}
		}
		return false;
	}
	
	/**
	 * 更新单个实体对象到数据库中,不级联更新其他实体对象
	 * @param  entity 实体对象
     * @param  isSelectiveUpdate  是否选择性字段更新（即：只更新指定不为空的字段）
	 * @return 更新成功返回true，否则返回false
	 * @throws Exception
	 */
	public boolean updateForSingle(Entity entity,boolean isSelectiveUpdate) throws Exception {
		if (entity == null)
			return false;
		if (Class.forName(entity.getClass().getTypeName()).isAnnotationPresent(EntityAttribute.class)) {

			EntityAttribute attribute = (EntityAttribute) Class.forName(entity.getClass().getTypeName())
					.getAnnotation(EntityAttribute.class);
			String updateTable = attribute.updateTable();
			String procedureName = "SP_" + updateTable + "_UPDATE";
			boolean isExitProcedure = SqlHelper.isExitProcedure(procedureName);
			String identityProperty = ReflectionUtils.getPropertyName(entity.getClass(),
					CUtils.underlineToCamel(attribute.identityProperty()));// entity实体中定义的关键字属性（例：private
																			// String
																			// id;）
			Class[] cascadeList = attribute.cascadeUpdateEntitys();/* 级联更新实体对象类集合 */
			String identityValue = (String) ReflectionUtils.getFieldValue(entity, identityProperty);

			if (isExitProcedure) {// 如果存在对应的存储过程
				try {
					DBParameterCollection params = SqlHelper.prepareParameters(procedureName, entity);
					int i = SqlHelper.executeNonQuery(procedureName, params);
					if (i == -1)
						return false;
					return true;
				} catch (Exception e) {
					e.printStackTrace();
					return false;
				}
			} else {
				String colName = "", updateColNames = "", updateValues = "", commandText = "";
				Object colValue = null;
                boolean colExit = true;
				StringBuilder sbUpdateColNames = new StringBuilder();
				ArrayList updateValuesList = new ArrayList();
				Field[] fieldList = entity.getClass().getDeclaredFields();
				try {
					for (Field field : fieldList) {
						
						field.setAccessible(true);
						
						if(ReflectionUtils.isStaticFinalField(field))//如果该字段使用了static final 修饰 【通常这么修饰的字段是用来定义常量的】
							continue;//	遍历下一个
						
						colName = "";
                        colExit = true;
						if (field.isAnnotationPresent(EntityFieldAttribute.class)) {
							EntityFieldAttribute fieldAttribute = (EntityFieldAttribute) field
									.getAnnotation(EntityFieldAttribute.class);
							colName = fieldAttribute.columnName();
                            colExit = fieldAttribute.exist();
						}
						if (colName.equals("")) {//如果没有设置属性与数据库字段对照(columnName属性值)时
							colName = CUtils.camelToUnderline(field.getName());
						}
						if(!colExit)//实体对象其属性字段中的属性不与数据库中的字段对应时
                            continue;

						colValue = ReflectionUtils.getFieldValue(entity, field.getName());
						if (identityProperty.equals(field.getName()))// 主键不参与
							continue;

						if(isSelectiveUpdate && null==colValue)//如果是选择性字段更新,则只更新不为空的字段
                            continue;
						
						if (field.getType().getSuperclass() == Entity.class) {//如果属性是另外类型的单个实体

						} else if (field.getType() == EntityCollection.class) {/* 如果属性是另外实体集合 */

						} else if (Collection.class.isAssignableFrom(field.getType())) {//如果属性是另外类型的List集合

						} else if (field.getType() == String.class) {
							sbUpdateColNames.append(colName + " =? ,");
							updateValuesList.add((String) colValue);
						} else if (field.getType() == Date.class) {
							sbUpdateColNames.append(colName + " =? ,");
							updateValuesList.add(DateUtils.format((Date) colValue));
						} else if (field.getType() == boolean.class || field.getType() == Boolean.class) {
							sbUpdateColNames.append(colName + " =? ,");
							updateValuesList.add(((Boolean) colValue) ? 1 : 0);
						} else {
							sbUpdateColNames.append(colName + " =? ,");
							updateValuesList.add(colValue);
						}
					}

					updateColNames = sbUpdateColNames.toString();
					if (updateColNames.endsWith(","))
						updateColNames = updateColNames.substring(0, updateColNames.length() - 1);

					commandText = "UPDATE " + updateTable + " SET " + updateColNames + " WHERE "
							+ CUtils.camelToUnderline(identityProperty) + " = " + "'" + identityValue + "'";
					int i = SqlHelper.excuteNonQuery(CommandType.Text, commandText, updateValuesList.toArray());
					if(i==-1)
						return false;
					return true;
				} catch (Exception e) {
					e.printStackTrace();
					return false;
				}
			}
		}
		
		return false;
	}


}
